<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"><title>mysql常用函数以及方法 | dmf的个人博客</title><meta name="author" content="dmf"><meta name="copyright" content="dmf"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="描述">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql常用函数以及方法">
<meta property="og:url" content="https://gitee.com/dmf-c/blog/2021/12/18/mysql/index.html">
<meta property="og:site_name" content="dmf的个人博客">
<meta property="og:description" content="描述">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://img1.baidu.com/it/u=3691791879,443411760&fm=253&fmt=auto&app=138&f=JPEG?w=500&h=889">
<meta property="article:published_time" content="2021-12-17T16:00:00.000Z">
<meta property="article:modified_time" content="2022-11-04T00:43:35.892Z">
<meta property="article:author" content="dmf">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://img1.baidu.com/it/u=3691791879,443411760&fm=253&fmt=auto&app=138&f=JPEG?w=500&h=889"><link rel="shortcut icon" href="/blog/img/favicon.png"><link rel="canonical" href="https://gitee.com/dmf-c/blog/2021/12/18/mysql/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/blog/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/blog/',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'mysql常用函数以及方法',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2022-11-04 08:43:35'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><meta name="generator" content="Hexo 6.3.0"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/blog/archives/"><div class="headline">文章</div><div class="length-num">27</div></a><a href="/blog/tags/"><div class="headline">标签</div><div class="length-num">0</div></a><a href="/blog/categories/"><div class="headline">分类</div><div class="length-num">6</div></a></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/blog/"><i class="fa-fw iconfont icon-shouyex"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw iconfont icon-zuixinwenzhang_huaban"></i><span> 找文章</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/blog/categories/"><i class="fa-fw iconfont icon-fenlei1"></i><span> 分类</span></a></li><li><a class="site-page child" href="/blog/tags/"><i class="fa-fw iconfont icon-biaoqian1"></i><span> 标签</span></a></li><li><a class="site-page child" href="/blog/archives/"><i class="fa-fw iconfont icon-shijianzhou"></i><span> 归档</span></a></li></ul></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://img1.baidu.com/it/u=3691791879,443411760&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=500&amp;h=889')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/blog/">dmf的个人博客</a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/blog/"><i class="fa-fw iconfont icon-shouyex"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw iconfont icon-zuixinwenzhang_huaban"></i><span> 找文章</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/blog/categories/"><i class="fa-fw iconfont icon-fenlei1"></i><span> 分类</span></a></li><li><a class="site-page child" href="/blog/tags/"><i class="fa-fw iconfont icon-biaoqian1"></i><span> 标签</span></a></li><li><a class="site-page child" href="/blog/archives/"><i class="fa-fw iconfont icon-shijianzhou"></i><span> 归档</span></a></li></ul></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">mysql常用函数以及方法</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="fa-fw post-meta-icon far fa-calendar-alt"></i><span class="post-meta-label">发表于</span><time datetime="2021-12-17T16:00:00.000Z" title="发表于 2021-12-18 00:00:00">2021-12-18</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/blog/categories/%E5%B7%A5%E5%85%B7%E7%B1%BB/">工具类</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="mysql常用函数以及方法"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h2 id="介绍"><a href="#介绍" class="headerlink" title="介绍"></a>介绍</h2><p>在平常使用MySQL的过程中，我们常常会使用到其中的函数。有些函数常用，就会非常熟悉，但有些不经常使用就会十分生疏。</p>
<p>避免使用到的时候，到处去翻博客，干脆自己总结，记录一下，顺便也加深一下自己的印象。</p>
<h2 id="常用函数"><a href="#常用函数" class="headerlink" title="常用函数"></a>常用函数</h2><h3 id="聚合函数"><a href="#聚合函数" class="headerlink" title="聚合函数"></a>聚合函数</h3><p>聚合函数又称为组函数，一般结合分组使用，用来统计和计算。<br>avg 计算平均值<br>select avg(num) from user;</p>
<p>count，计算行数<br>select count(1) from tb_user;<br>select count(*) from tb_user;<br>select count(name) from user;</p>
<p>max，最大数<br>select max(name) from user;</p>
<p>min，最小数<br>select min(name) from user;</p>
<p>sum，求和<br>select sum(name) from user;</p>
<p>group_concat 分组聚合，以逗号分隔<br>select nf, group_concat(name) from user group by nf;</p>
<p>聚合函数不会对null值进行计算，会略过null值。</p>
<h3 id="字符串函数"><a href="#字符串函数" class="headerlink" title="字符串函数"></a>字符串函数</h3><p>concat 合并字符串<br>select concat(‘11’, ‘22’, ‘33’); – 112233<br>select concat(‘11’, null, ‘22’); – null</p>
<p>concat_ws，合并字符串，与上面不同的是，此处第一个参数是分隔符<br>select concat_ws(‘-‘, ‘11’, ‘22’, ‘33’); – 11-22-33<br>select concat_ws(null, ‘11’, ‘22’, ‘33’); – null<br>select concat_ws(‘-‘, ‘11’, null, ‘22’); – 11-22</p>
<p>length，返回字符串的字节长度<br>select length(‘123’); – 3<br>select length(‘数据库’); – 9</p>
<p>reverse，反转字符串<br>select reverse(‘123’); – 321<br>select reverse(‘数据库’); – 库据数</p>
<p>lower，将字符串中的所有字母都变成小写<br>select upper(‘ABC’); – abc</p>
<p>upper，将字符串中的所有字母都变成大写<br>select upper(‘abc’); – ABC</p>
<p>left，从左到右进行截取指定数量的字符<br>select left(‘123’, 2); – 12</p>
<p>right，从右到左进行截取指定数量的字符<br>select right(‘123’, 2); – 23</p>
<p>lpad，从字符串左边进行填充，直到长度到达指定字符长度<br>select lpad(‘2’, 5, ‘0’); – 00002</p>
<p>rpad，从字符串右边进行填充，直到长度到达指定字符长度<br>select rpad(‘1’, 5, ‘0’); – 20000</p>
<p>ltrim，去除字符串右边的空格<br>select ltrim(‘ 1234’); – 1234</p>
<p>rtrim，去除字符串右边的空格<br>select rtrim(‘1234 ‘); – 1234</p>
<p>trim，去除字符串左右两边的空格<br>select trim(‘ 1234 ‘); – 1234</p>
<p>repeat，将字符串重复指定的次数<br>select repeat(‘1234’, 3); – 123412341234</p>
<p>replace，将字符串替换其中指定的字符<br>select replace(‘我爱学习’, ‘爱’, ‘不爱’); – 我不爱学习</p>
<p>strcmp，字符串比较<br>select strcmp(‘abc’, ‘abc’); – 0，相等返回0<br>select strcmp(‘abc’, ‘abcd’); – -1，前字符串&lt;后字符串 返回1<br>select strcmp(‘abcd’, ‘abc’); – 1，前字符串&gt;后字符串 返回-1<br>select strcmp(‘abc’, null); – null，有null值计算，返回null</p>
<p>substring，截取字符串，从指定的位置开始，截取指定数量的字符<br>select substring(‘012345’, 2, 1); – 1<br>select substring(‘012345’, 2, 4); – 1234</p>
<p>insert，替换字符串，将从指定的位置开始，到指定的数量的字符串，替换为新字符串<br>select insert(‘0123456’, 2, 1, ‘A’);– 0A23456<br>select insert(‘0123456’, 2, 4, ‘ABCD’);– 0ABCD56</p>
<h3 id="数字计算函数"><a href="#数字计算函数" class="headerlink" title="数字计算函数"></a>数字计算函数</h3><p>abs，绝对值<br>select abs(-1); – 1<br>select abs(1); – 1</p>
<p>sqrt, 开平方<br>select sqrt(4); – 2<br>select sqrt(9); – 3</p>
<p>mod，求余数<br>select MOD(9, 2); – 1</p>
<p>ceil、ceiling，返回整数，向上取整<br>select ceil(3.14); – 4<br>select ceiling(3.14); – 4</p>
<p>floor，返回整数，向下取整<br>select floor(3.14); – 3</p>
<p>round，四舍五入<br>select round(3.14); – 3<br>select round(2.76); – 3<br>select round(2.8828, 2); – 2.88<br>select round(2.8828, 3); – 2.883</p>
<p>rand，返回一个0-1之间的随机数<br>select rand(); – 0.5440051923915792</p>
<p>sign，返回参数的符号，负整数返回-1，正整数返回1，0返回0<br>select sign(0); – 0<br>select sign(88); – 1<br>select sign(-7); – -1</p>
<p>pow、power，幂次方<br>select pow(2, 3); – 8<br>select power(2, 3); – 8</p>
<p>pi，圆周率π<br>select pi(); – 3.141593</p>
<p>sin，正弦值<br>select sin(0.5*pi()); – 1<br>select sin(1); – 0.8414709848078965</p>
<p>asin，反正弦值<br>select asin(0.8414709848078965) – 1<br>select asin(2); – null</p>
<p>cos，余弦值<br>select cos(0); – 1<br>select cos(pi()); – -1<br>select cos(1); – 0.5403023058681398</p>
<p>acos，反余弦值<br>select acos(1); – 0<br>select acos(-1); – 3.141592653589793<br>select acos(2); – null</p>
<p>tan，正切值<br>select tan(0); – 0<br>select tan(1); – 1.5574077246549023</p>
<p>atan，反正切值<br>select atan(0); – 0<br>select atan(1, 1); – 0.7853981633974483</p>
<p>cot，余切值<br>select cot(1); – 0.6420926159343306</p>
<h3 id="判断函数"><a href="#判断函数" class="headerlink" title="判断函数"></a>判断函数</h3><p>if，判断函数<br>select if(88&gt;60, ‘合格’, ‘不合格’); – 合格<br>select if(59&gt;60, ‘合格’, ‘不合格’); – 不合格</p>
<p>ifnull，判断是否为空<br>select ifnull(null, 0); – null<br>select ifnull(55, 0); – 55</p>
<p>case when，也是个流程控制的，但不属于函数<br>select case<br>when 80&gt;90 then ‘优秀’<br>when 80&gt;70 then ‘良好’<br>when 80&gt;60 then ‘一般’<br>else ‘差劲’ end as rate;– 良好</p>
<h3 id="日期函数"><a href="#日期函数" class="headerlink" title="日期函数"></a>日期函数</h3><p>curdate、current_date，返回当前日期<br>select curdate(); – 2022-09-09<br>select current_date(); – 2022-09-09</p>
<p>curtime、current_time，返回当前时间<br>select curtime(); – 22:04:30</p>
<p>now、sysdate，返回当前日期时间<br>select now(); – 2022-09-09 08:58:46<br>select sysdate(); – 2022-09-09 08:58:56</p>
<p>unix_timestamp，返回当前unix时间戳<br>select unix_timestamp(); – 1662685149</p>
<p>from_unixtime，格式化unix时间戳<br>select from_unixtime(1662685149); – 2022-09-09 08:59:09<br>select from_unixtime(1662685149, ‘%Y-%m-%d’); – 2022-09-09 08:59:09</p>
<p>year，返回指定日期的年份<br>select year(now()); – 2022</p>
<p>month，返回指定日期的月份<br>select month(now()); – 9</p>
<p>monthname，返回指定日期的月份英文名<br>select monthname(now()); – September</p>
<p>day、dayofmonth，返回指定日期是一月中的第几天<br>select day(now()); – 9<br>select dayofmonth(now()); – 9</p>
<p>dayofyear，返回指定日期是一年中的第几天<br>select dayofyear(now()); – 252</p>
<p>dayofweek，返回指定日期在一周的第几天，星期天是第一天，星期一是第二天<br>select dayofweek(‘2022-09-09 10:00:00’); – 6<br>select dayofweek(‘2022-09-10 10:00:00’); – 7</p>
<p>weekday，返回指定日期在一周内的索引值，星期一是0，星期日是6<br>select dayofweek(‘2022-09-09 10:00:00’); – 6<br>select dayofweek(‘2022-09-10 10:00:00’); – 7</p>
<p>week，返回指定日期是一年中的第几周<br>select week(now()); – 36</p>
<p>dayname，返回指定日期是星期几英文名<br>select dayname(now()); – Friday</p>
<p>time_to_sec，返回指定时间的秒数<br>select time_to_sec(‘00:00:11’); – 11</p>
<p>sec_to_time，返回指定秒数的时间<br>select sec_to_time(11); – 00:00:11</p>
<p>date_add、adddate，将指定日期添加指定的时间间隔<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL 30 SECOND); – 2022-09-09 10:00:30<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL ‘30:30’ MINUTE_SECOND); – 2022-09-09 10:30:30<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL 30 MINUTE); – 2022-09-09 10:30:00<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL 2 HOUR); – 2022-09-09 12:00:00<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL 2 DAY); – 2022-09-11 10:00:00<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL 1 MONTH); – 2022-10-09 10:00:00<br>select date_add(‘2022-09-09 10:00:00’, INTERVAL 1 YEAR); – 2023-09-09 10:00:00</p>
<p>date_sub、subdate，将指定日期减去指定的时间间隔<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL 30 SECOND); – 2022-09-09 09:59:30<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL ‘30:30’ MINUTE_SECOND); – 2022-09-09 09:29:30<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL 30 MINUTE); – 2022-09-09 09:30:00<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL 2 HOUR); – 2022-09-09 08:00:00<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL 2 DAY); – 2022-09-07 10:00:00<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL 1 MONTH); – 2022-08-09 10:00:00<br>select date_sub(‘2022-09-09 10:00:00’, INTERVAL 1 YEAR); – 2021-09-09 10:00:00</p>
<p>addtime，将指定的日期添加指定的时间<br>select addtime(‘2022-09-09 10:00:00’, ‘01:30:30’); – 2022-09-09 11:30:30</p>
<p>subtime，将指定的日期添加指定的时间<br>select subtime(‘2022-09-09 10:00:00’, ‘01:30:30’); – 2022-09-09 08:29:30</p>
<p>datediff，返回两个日期间相差的天数，参数1-参数2的日期<br>select datediff(‘2022-09-09 10:00:00’, ‘2022-05-02 10:00:00’); – 130<br>select datediff(‘2022-09-09 23:59:59’, ‘2022-10-09 00:00:00’); – -30</p>
<p>date_format，返回日期格式化<br>select date_format(now(), ‘%Y-%m-%d’); – 2022-09-09<br>select date_format(now(), ‘%Y-%m-%d %T’); – 2022-09-09 09:09:19<br>select date_format(now(), ‘%Y-%m-%d %H:%i:%s’); – 2022-09-09 09:09:29</p>
<h3 id="类型转换函数"><a href="#类型转换函数" class="headerlink" title="类型转换函数"></a>类型转换函数</h3><p>cast、convert，类型转换函数<br>转换为浮点<br>select cast(‘2022.87’ as DECIMAL); – 2023<br>select cast(‘2022.87’ as DECIMAL(11, 2)); – 2022.87<br>转换为整数<br>select cast(‘2022.87’ as SIGNED); – 2022<br>select cast(‘-2022.87’ as SIGNED); – -2022<br>select cast(‘2022.87’ as UNSIGNED); – 2022<br>select cast(‘-2022.87’ as UNSIGNED); – 18446744073709549594，无符号整数类型不支持负数<br>字符串转为日期、时间、日期时间，格式必须是yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss<br>select cast(‘2022-05-01 10:00:00’ as DATE); – 2022-05-01<br>select cast(‘2022-05-01 10:00:00’ as TIME); – 10:00:00<br>select cast(‘2022-05-01 10:00:00’ as DATETIME); – 2022-05-01 10:00:00<br>数字转为字符串<br>select cast(123 as CHAR); – 123<br>select cast(123 as BINARY); – 123</p>
<p>convert，字符集转换<br>select convert(‘abc’ USING utf8); – abc<br>select convert(‘你好’ USING latin1); – 乱码</p>
<p>不是函数，但可以使用加号类解决<br>select ‘123’ + 0; – 123，数字类型</p>
<p>concat、可以使数字合并为字符串<br>select concat(0, ‘’); – 0，字符串类型</p>
<p>date_format;可以使日期时间格式化为字符串</p>
<h3 id="系统函数"><a href="#系统函数" class="headerlink" title="系统函数"></a>系统函数</h3><p>version，版本号<br>select version(); – 5.7.35-log</p>
<p>connection_id，连接id<br>select connection_id(); – 7523</p>
<p>user、system_user、session_user，连接的用户<br>select user(); – <a href="mailto:&#x71;&#108;&#x73;&#x79;&#64;&#x31;&#x31;&#55;&#x2e;&#56;&#49;&#x2e;&#55;&#x37;&#x2e;&#x32;&#x33;&#x30;">&#x71;&#108;&#x73;&#x79;&#64;&#x31;&#x31;&#55;&#x2e;&#56;&#49;&#x2e;&#55;&#x37;&#x2e;&#x32;&#x33;&#x30;</a><br>select system_user(); – <a href="mailto:&#113;&#108;&#115;&#121;&#64;&#49;&#49;&#x37;&#x2e;&#56;&#x31;&#x2e;&#55;&#55;&#x2e;&#50;&#x33;&#48;">&#113;&#108;&#115;&#121;&#64;&#49;&#49;&#x37;&#x2e;&#56;&#x31;&#x2e;&#55;&#55;&#x2e;&#50;&#x33;&#48;</a><br>select session_user(); – <a href="mailto:&#113;&#x6c;&#x73;&#121;&#x40;&#x31;&#49;&#x37;&#x2e;&#x38;&#x31;&#46;&#x37;&#55;&#x2e;&#x32;&#51;&#x30;">&#113;&#x6c;&#x73;&#121;&#x40;&#x31;&#49;&#x37;&#x2e;&#x38;&#x31;&#46;&#x37;&#55;&#x2e;&#x32;&#51;&#x30;</a></p>
<p>current_user，连接允许的主机<br>select current_user(); – qlsy@%</p>
<p>database、schema，<br>select database(); – systemdata<br>select schema(); – systemdata</p>
<p>inet_ntoa，将IPv4地址的dotted-quad表示形式作为字符串，并以整数形式返回给定IP地址的数值<br>select inet_aton(‘192.168.0.101’); – 3232235621<br>select inet_aton(‘-111’); – null</p>
<p>inet_ntoa，以网络字节顺序获取IPv4地址，然后以dotted-quad字符串表示形式返回该地址<br>select inet_ntoa(‘3232235621’); – 192.168.0.101<br>select inet_ntoa(‘-222’); – null</p>
<p>last_insert_id，获取上次插入的主键<br>select last_insert_id(); – 0</p>
<p>md5，md5加密<br>select md5(‘123456’); – e10adc3949ba59abbe56e057f20f883e</p>
<p>password，加密<br>select password(‘123456’); – *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9</p>
<p>encode，加密<br>select encode(‘123456’, ‘abc’);</p>
<p>decode，解密<br>select decode(encode(‘123456’, ‘abc’), ‘abc’) – 123456</p>
<h2 id="常用方法"><a href="#常用方法" class="headerlink" title="常用方法"></a>常用方法</h2><h3 id="查询语句"><a href="#查询语句" class="headerlink" title="查询语句"></a>查询语句</h3><p>1.查询一张表： select * from 表名；</p>
<p>2.查询指定字段：select 字段1，字段2，字段3….from 表名；</p>
<p>3.where条件查询：select 字段1，字段2，字段3 frome 表名 where 条件表达式；<br>例：select * from t_studect where id&#x3D;1;<br>select * from t_student where age&gt;22;</p>
<p>4.带in关键字查询：select 字段1，字段2 frome 表名 where 字段 [not]in(元素1，元素2)；<br>例：select * from t_student where age in (21,23);<br>select * from t_student where age not in (21,23);</p>
<p>5.带between and的范围查询：select 字段1，字段2 frome 表名 where 字段 [not]between 取值1 and 取值2；<br>例：select * frome t_student where age between 21 and 29;<br>select * frome t_student where age not between 21 and 29;</p>
<p>6.带like的模糊查询：select 字段1，字段2… frome 表名 where 字段 [not] like ‘字符串’；<br>“%”代表任意字符；<br>“_”代表单个字符；<br>例：select * frome t_student where stuName like ‘张三”；<br>select * frome t_student where stuName like ‘张三%”；<br>select * frome t_student where stuName like ‘%张三%”；&#x2F;&#x2F;含有张三的任意字符<br>select * frome t_student where stuName like ‘张三_”</p>
<p>7.空值查询：select 字段1，字段2…frome 表名 where 字段 is[not] null;</p>
<p>8.带and的多条件查询：<br>select 字段1，字段2…frome 表名 where 条件表达式1 and 条件表达式2 [and 条件表达式n]<br>例：select * frome t_student where gradeName&#x3D;’一年级’ and age&#x3D;23；</p>
<p>9.带or的多条件查询<br>select 字段1，字段2…frome 表名 where 条件表达式1 or 条件表达式2 [or 条件表达式n]<br>例：select * frome t_student where gradeName&#x3D;’一年级’ or age&#x3D;23；&#x2F;&#x2F;或者，条件只要满足一个</p>
<p>10.distinct去重复查询：select distinct 字段名 from 表名；</p>
<p>11.对查询结果排序order by：select 字段1，字段2…from 表名 order by 属性名 [asc|desc]<br>例：select * frome t_student order by age desc；&#x2F;&#x2F;降序，从大到小<br>select * frome t_student order by age asc；&#x2F;&#x2F;升序，asc默认可以不写</p>
<p>12.分组查询group by<br>group by 属性名 [having 条件表达式][with rollup]<br>1.单独使用（毫无意义，不能单独使用）；</p>
<h3 id="模糊查询"><a href="#模糊查询" class="headerlink" title="模糊查询"></a>模糊查询</h3><p>一、 %：表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百 分号（%%）表示。</p>
<p>*注：like ‘%龙%’：查询出所有含有“龙”的记录</p>
<p>所查询字段 + like ‘%龙%’<br>eg:select * from user where realname like ‘%龙%</p>
<p>所查询字段 + like ‘%龙%’ and 所查询字段 + like ‘%文%’<br>eg: select * from user where realname like ‘%龙%’ and realname like ‘%文%’<br>&#x3D; 所查询字段 + like ‘%文%’ and 所查询字段 + like ‘%龙%’<br>eg: select * from user where realname like ‘%文%’ and realname like ‘%龙%’</p>
<p>查询出既含有“龙”同时又有“文”的所有记录<br>realname like’%龙%文%’：查询所有“龙文”记录，可以是’龙_文’ ‘_ 龙文’ ‘龙文_’<br>“龙”字须在前面，“文”字在后面<br>select * from user where realname like ‘%龙%文%’</p>
<p>二、表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句</p>
<p>1.查询出中间含有“林”字的realname字段(前提：名字三个字)<br>select * from user where realname like ‘林‘<br>&#x3D;select * from user where realname like ‘%林%’</p>
<p>2.查询出姓林的姓名(名字三个字)<br>select * from user where realname like’林__’(这里有两横杠)<br>&#x3D;select * from user where realname like ‘%林__%’(这里有两横杠)<br>查询出姓“林”的realname，且realname的字数必须为3</p>
<p>3.查询名字最后一个字为“林”的姓名(名字三个字)<br>select * from user where realname like ‘__林’(这里有两横杠)<br>&#x3D; select * from user where realname like ‘%__林%’(这里有两横杠)<br>查询出姓名最后一个字为“林”的姓名，且realname的字数必须为3</p>
<p>4.查询出姓林的姓名（姓名只有两个字）<br>select * from user where realname like ‘林_’</p>
<p>查询出姓林的姓名（姓名可以是两个字，也可以是三个字）<br>select * from user where realname like ‘%林_%’</p>
<p>5.查询名字最后一个字为“林”的姓名（姓名只有两个字）<br>select * from user where realname like ‘_林’</p>
<p>查询名字最后一个字为“林”的姓名（姓名可以有两个字，也可以是三个字）<br>select * from user where realname like ‘%_林%’</p>
<h3 id="查询语句id自增"><a href="#查询语句id自增" class="headerlink" title="查询语句id自增"></a>查询语句id自增</h3><p>SELECT @rownum:&#x3D;@rownum+1 AS ‘id’, a.* FROM user a ,(SELECT @rownum:&#x3D;0) r;</p>
<h3 id="替换-中间4位为星号显示"><a href="#替换-中间4位为星号显示" class="headerlink" title="替换*中间4位为星号显示"></a>替换*中间4位为星号显示</h3><p>select REPLACE(tel,SUBSTR(tel,4,4),’****’) tel from rwork_uinfo where comp_id &#x3D; 3722</p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="https://gitee.com/dmf-c/blog">dmf</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://gitee.com/dmf-c/blog/2021/12/18/mysql/">https://gitee.com/dmf-c/blog/2021/12/18/mysql/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://gitee.com/dmf-c/blog" target="_blank">dmf的个人博客</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"></div><div class="post_share"><div class="social-share" data-image="https://img1.baidu.com/it/u=3691791879,443411760&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=500&amp;h=889" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/blog/2022/01/02/vue_file/assembly/"><img class="prev-cover" src="https://img1.baidu.com/it/u=1828439677,2166285235&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=400&amp;h=300" onerror="onerror=null;src='/blog/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">vue组件传值的多种方式</div></div></a></div><div class="next-post pull-right"><a href="/blog/2021/11/28/MyBlog/"><img class="next-cover" src="https://img1.baidu.com/it/u=3691791879,443411760&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=500&amp;h=889" onerror="onerror=null;src='/blog/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">gitee+hexo搭建个人博客</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="https://i.loli.net/2021/02/24/5O1day2nriDzjSu.png" onerror="this.onerror=null;this.src='/blog/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">dmf</div><div class="author-info__description"></div></div><div class="card-info-data site-data is-center"><a href="/blog/archives/"><div class="headline">文章</div><div class="length-num">27</div></a><a href="/blog/tags/"><div class="headline">标签</div><div class="length-num">0</div></a><a href="/blog/categories/"><div class="headline">分类</div><div class="length-num">6</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>公告</span></div><div class="announcement_content">业精于勤，荒于嬉。</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BB%8B%E7%BB%8D"><span class="toc-number">1.</span> <span class="toc-text">介绍</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%B8%B8%E7%94%A8%E5%87%BD%E6%95%B0"><span class="toc-number">2.</span> <span class="toc-text">常用函数</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0"><span class="toc-number">2.1.</span> <span class="toc-text">聚合函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%87%BD%E6%95%B0"><span class="toc-number">2.2.</span> <span class="toc-text">字符串函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%95%B0%E5%AD%97%E8%AE%A1%E7%AE%97%E5%87%BD%E6%95%B0"><span class="toc-number">2.3.</span> <span class="toc-text">数字计算函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A4%E6%96%AD%E5%87%BD%E6%95%B0"><span class="toc-number">2.4.</span> <span class="toc-text">判断函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0"><span class="toc-number">2.5.</span> <span class="toc-text">日期函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B1%BB%E5%9E%8B%E8%BD%AC%E6%8D%A2%E5%87%BD%E6%95%B0"><span class="toc-number">2.6.</span> <span class="toc-text">类型转换函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%B3%BB%E7%BB%9F%E5%87%BD%E6%95%B0"><span class="toc-number">2.7.</span> <span class="toc-text">系统函数</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%B8%B8%E7%94%A8%E6%96%B9%E6%B3%95"><span class="toc-number">3.</span> <span class="toc-text">常用方法</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5"><span class="toc-number">3.1.</span> <span class="toc-text">查询语句</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2"><span class="toc-number">3.2.</span> <span class="toc-text">模糊查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5id%E8%87%AA%E5%A2%9E"><span class="toc-number">3.3.</span> <span class="toc-text">查询语句id自增</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9B%BF%E6%8D%A2-%E4%B8%AD%E9%97%B44%E4%BD%8D%E4%B8%BA%E6%98%9F%E5%8F%B7%E6%98%BE%E7%A4%BA"><span class="toc-number">3.4.</span> <span class="toc-text">替换*中间4位为星号显示</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/blog/2023/01/08/vue_file/vuenote/" title="vue3入门笔记"><img src="https://img1.baidu.com/it/u=1828439677,2166285235&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=400&amp;h=300" onerror="this.onerror=null;this.src='/blog/img/404.jpg'" alt="vue3入门笔记"/></a><div class="content"><a class="title" href="/blog/2023/01/08/vue_file/vuenote/" title="vue3入门笔记">vue3入门笔记</a><time datetime="2023-01-07T16:00:00.000Z" title="发表于 2023-01-08 00:00:00">2023-01-08</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/blog/2022/02/20/vue_file/comm/" title="vue组件间通信六种方式"><img src="https://img1.baidu.com/it/u=1828439677,2166285235&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=400&amp;h=300" onerror="this.onerror=null;this.src='/blog/img/404.jpg'" alt="vue组件间通信六种方式"/></a><div class="content"><a class="title" href="/blog/2022/02/20/vue_file/comm/" title="vue组件间通信六种方式">vue组件间通信六种方式</a><time datetime="2022-02-19T16:00:00.000Z" title="发表于 2022-02-20 00:00:00">2022-02-20</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/blog/2022/02/05/wxEcharts/" title="微信小程序使用 echarts"><img src="https://img1.baidu.com/it/u=1828439677,2166285235&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=400&amp;h=300" onerror="this.onerror=null;this.src='/blog/img/404.jpg'" alt="微信小程序使用 echarts"/></a><div class="content"><a class="title" href="/blog/2022/02/05/wxEcharts/" title="微信小程序使用 echarts">微信小程序使用 echarts</a><time datetime="2022-02-04T16:00:00.000Z" title="发表于 2022-02-05 00:00:00">2022-02-05</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/blog/2022/01/30/vue_file/vue_router/" title="vue-router学习"><img src="https://img1.baidu.com/it/u=1828439677,2166285235&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=400&amp;h=300" onerror="this.onerror=null;this.src='/blog/img/404.jpg'" alt="vue-router学习"/></a><div class="content"><a class="title" href="/blog/2022/01/30/vue_file/vue_router/" title="vue-router学习">vue-router学习</a><time datetime="2022-01-29T16:00:00.000Z" title="发表于 2022-01-30 00:00:00">2022-01-30</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/blog/2022/01/16/vue_file/vuex/" title="Vuex使用方法"><img src="https://img1.baidu.com/it/u=1828439677,2166285235&amp;fm=253&amp;fmt=auto&amp;app=138&amp;f=JPEG?w=400&amp;h=300" onerror="this.onerror=null;this.src='/blog/img/404.jpg'" alt="Vuex使用方法"/></a><div class="content"><a class="title" href="/blog/2022/01/16/vue_file/vuex/" title="Vuex使用方法">Vuex使用方法</a><time datetime="2022-01-15T16:00:00.000Z" title="发表于 2022-01-16 00:00:00">2022-01-16</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2023 By dmf</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/blog/js/utils.js"></script><script src="/blog/js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.min.js"></script><div class="js-pjax"></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>